﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Drawing;
using NUnit.Framework;
using Smartkernel;
using Smartkernel.Attributes;
using Smartkernel.Data;

namespace Smartkernel.NUnitTest.Data
{
    [Category("Data")]
    [TestFixture]
    public class SmartDatabaseTest
    {
        [Test]
        public void Test()
        {
            #region 测试
            ////注意，只提供了SqlServer数据库的测试，因为代码基本一样，所以应该无太大问题。我现在的项目主要使用SqlServer，所以其他几种数据库的测试就不提供了。

            ////【01】直接通过SQL语句插入一条数据（涉及方法：MappingOR、ExecuteNonQuery、FindDbParameter）
            //string sqlConnectionString = SmartCoreEntity.Instance.ConnectionString;
            //var person1 = new { ID = SmartParameterDirection.Output, Name = "SmartKernel1", BirthDay = DateTime.Parse("1982-05-02"), Money = 125.56, EMails = "caoyanbai@gmail.com|smartkernel@163.com|smartkernel@gmail.com", LoveColor = "#CCCCCC", LoveDay = DayOfWeek.Monday.ToString() };
            //DbParameter[] parameters1 = SmartDatabase.MappingOR(person1, SmartMappingType.Property).ToArray();
            //SmartDatabase.ExecuteNonQuery(sqlConnectionString, "INSERT INTO Person (Name,BirthDay,EMails,LoveDay,LoveColor,Money) VALUES (@Name,@BirthDay,@EMails,@LoveDay,@LoveColor,@Money);SET @ID = SCOPE_IDENTITY();", parameters1);
            //Assert.AreEqual(Convert.ToInt32(SmartDatabase.FindDbParameter(parameters1, "ID").Value) > 0, true);

            ////【02】通过存储过程插入一条数据（涉及方法：MappingOR、ExecuteNonQuery、FindDbParameter）
            //var person2 = new { ID = SmartParameterDirection.Output, Name = "SmartKernel2", BirthDay = DateTime.Parse("1982-05-02"), Money = 125.56, EMails = "caoyanbai@gmail.com|smartkernel@163.com|smartkernel@gmail.com", LoveColor = "#CCCCCC", LoveDay = DayOfWeek.Monday.ToString() };
            //DbParameter[] parameters2 = SmartDatabase.MappingOR(person2, SmartMappingType.Property, SmartDatabaseType.SqlServer).ToArray();
            //SmartDatabase.ExecuteNonQuery(sqlConnectionString, "EXEC AddPerson @Name,@BirthDay,@EMails,@LoveDay,@LoveColor,@Money,@ID OUTPUT;", parameters2, SmartDatabaseType.SqlServer);
            //Assert.AreEqual(Convert.ToInt32(SmartDatabase.FindDbParameter(parameters2, "ID", SmartDatabaseType.SqlServer).Value) > 0, true);

            ////【03】直接通过SQL获取数据集（涉及方法：Fill）
            //DataTable dataTable3 = new DataTable();
            //SmartDatabase.Fill(sqlConnectionString, "SELECT TOP 1 * FROM Person ORDER BY NEWID();", dataTable3);
            //Assert.AreEqual(dataTable3.Rows.Count, 1);

            ////【04】通过SQL的查询条件获取数据集（涉及方法：MappingOR、Fill）
            //DataSet dataSet4 = new DataSet();
            //decimal money = 100M;
            //var condition4 = new { Name = "S", Money = money };
            //DbParameter[] parameters4 = SmartDatabase.MappingOR(condition4, SmartMappingType.Property, SmartDatabaseType.SqlServer).ToArray();
            //SmartDatabase.Fill(sqlConnectionString, "SELECT * FROM Person WHERE Name LIKE '%'+@Name+'%' AND Money > @Money", dataSet4, parameters4);
            //Assert.AreEqual(dataSet4.Tables[0].Rows.Count > 0, true);

            ////【05】获取总人数（涉及方法：ExecuteScalar）
            //Nullable<int> result5 = (Nullable<int>)SmartDatabase.ExecuteScalar(sqlConnectionString, "SELECT COUNT(*) FROM Person", SmartDatabaseType.SqlServer);
            //Assert.AreEqual(result5 != null, true);
            //Assert.AreEqual(result5 > 0, true);

            ////【06】获取一个实体对象（涉及方法：MappingOR、Fill、CloneDbParameters）
            //DataTable dataTable6 = new DataTable();
            //var condition6 = new { Name = "S" };
            //DbParameter[] parameters6 = SmartDatabase.MappingOR(condition6, SmartMappingType.Property, SmartDatabaseType.SqlServer).ToArray();
            //parameters6 = SmartDatabase.CloneDbParameters(parameters6, SmartDatabaseType.SqlServer);
            //SmartDatabase.Fill(sqlConnectionString, "SELECT TOP 1 * FROM Person WHERE Name LIKE '%'+@Name+'%' ORDER BY NEWID();", dataTable6, parameters6, SmartDatabaseType.SqlServer);
            //Person person6 = SmartDatabase.MappingRO<Person>(dataTable6.Rows[0], SmartMappingType.Attribute);
            //Assert.AreEqual(person6.Name.StartsWith("SmartKernel"), true);
            //Assert.AreEqual(person6.ID > 0, true);
            //Assert.AreEqual(person6.LoveBook, "史记");
            //Assert.AreEqual(person6.LoveColor.Name, "#CCCCCC");
            //Assert.AreEqual(person6.LoveSong, null);
            //Assert.AreEqual(person6.Money, 125.56m);
            //Assert.AreEqual(person6.EMails.Count, 3);

            ////【07】获得一组实体对象（涉及方法：MappingOR、Fill、CloneDbParameters）
            //DataTable dataTable7 = new DataTable();
            //var condition7 = new { Name = "S" };
            //DbParameter[] parameters7 = SmartDatabase.MappingOR(condition7, SmartMappingType.Property, SmartDatabaseType.SqlServer).ToArray();
            //parameters7 = SmartDatabase.CloneDbParameters(parameters7, SmartDatabaseType.SqlServer);
            //SmartDatabase.Fill(sqlConnectionString, "SELECT TOP 3 * FROM Person WHERE Name LIKE '%'+@Name+'%' ORDER BY NEWID();", dataTable7, parameters7, SmartDatabaseType.SqlServer);
            //List<Person> list7 = SmartDatabase.MappingROList<Person>(dataTable7.Rows, SmartMappingType.Attribute);
            //list7.ForEach(person7 =>
            //{
            //    Assert.AreEqual(person7.Name.StartsWith("SmartKernel"), true);
            //    Assert.AreEqual(person7.ID > 0, true);
            //    Assert.AreEqual(person7.LoveBook, "史记");
            //    Assert.AreEqual(person7.LoveColor.Name, "#CCCCCC");
            //    Assert.AreEqual(person7.LoveSong, null);
            //    Assert.AreEqual(person7.Money, 125.56m);
            //    Assert.AreEqual(person7.EMails.Count, 3);
            //});

            ////【08】事务处理的标准实现（涉及方法：MappingOR、ExecuteNonQuery、TransactionScope）
            //Action action8_1 = delegate
            //{
            //    var person8 = new { ID = SmartParameterDirection.Output, Name = "SmartKernel8", BirthDay = DateTime.Parse("1982-05-02"), Money = 125.56, EMails = "caoyanbai@gmail.com|smartkernel@163.com|smartkernel@gmail.com", LoveColor = "#CCCCCC", LoveDay = DayOfWeek.Monday.ToString() };
            //    DbParameter[] parameters8 = SmartDatabase.MappingOR(person8, SmartMappingType.Property).ToArray();
            //    SmartDatabase.ExecuteNonQuery(sqlConnectionString, "INSERT INTO Person (Name,BirthDay,EMails,LoveDay,LoveColor,Money) VALUES (@Name,@BirthDay,@EMails,@LoveDay,@LoveColor,@Money);SET @ID = SCOPE_IDENTITY();", parameters8);
            //};
            //Action action8_2 = delegate
            //{
            //    var person8 = new { ID = SmartParameterDirection.Output, Name = "SmartKernel8", BirthDay = DateTime.Parse("1982-05-02"), Money = 125.56, EMails = "caoyanbai@gmail.com|smartkernel@163.com|smartkernel@gmail.com", LoveColor = "#CCCCCC", LoveDay = DayOfWeek.Monday.ToString() };
            //    DbParameter[] parameters8 = SmartDatabase.MappingOR(person8, SmartMappingType.Property).ToArray();
            //    SmartDatabase.ExecuteNonQuery(sqlConnectionString, "INSERT INTO Person (ErrorName,BirthDay,EMails,LoveDay,LoveColor,Money) VALUES (@Name,@BirthDay,@EMails,@LoveDay,@LoveColor,@Money);SET @ID = SCOPE_IDENTITY();", parameters8);
            //};
            //Assert.AreEqual(SmartAction.IsInvokeFail(delegate { SmartDatabase.TransactionScope(action8_1, action8_2); }), true);
            //Assert.AreEqual(Convert.ToInt32(SmartDatabase.ExecuteScalar(sqlConnectionString, "SELECT COUNT(*) FROM Person WHERE Name = 'SmartKernel8'")), 0);

            ////【09】清理数据库（涉及方法：ExecuteNonQuery）
            //SmartDatabase.ExecuteNonQuery(sqlConnectionString, "TRUNCATE TABLE Person;");
            #endregion
        }

        private class Person
        {
            private string name;
            [SmartColumnMapping("Name")]
            public string Name
            {
                get { return this.name; }
                set { this.name = value; }
            }

            [SmartColumnMapping("BirthDay")]
            private DateTime birthDay;
            public DateTime BirthDay
            {
                get { return this.birthDay; }
                set { this.birthDay = value; }
            }

            private int id;
            [SmartColumnMapping("ID")]
            public int ID
            {
                get { return this.id; }
                set { this.id = value; }
            }

            [SmartColumnMapping("Money")]
            private decimal money;
            public decimal Money
            {
                get { return this.money; }
                set { this.money = value; }
            }

            private List<string> emails;
            [SmartColumnMapping("EMails")]
            public List<string> EMails
            {
                get { return this.emails; }
                set { this.emails = value; }
            }

            [SmartColumnMapping("LoveColor")]
            private Color loveColor;
            public Color LoveColor
            {
                get { return this.loveColor; }
                set { this.loveColor = value; }
            }

            private DayOfWeek loveDay;
            [SmartColumnMapping("LoveDay")]
            public DayOfWeek LoveDay
            {
                get { return this.loveDay; }
                set { this.loveDay = value; }
            }

            private string loveSong;
            [SmartColumnMapping("LoveSong")]
            public string LoveSong
            {
                get { return this.loveSong; }
                set { this.loveSong = value; }
            }

            private string loveBook;
            [SmartColumnMapping("LoveBook", DefaultValue = "史记")]
            public string LoveBook
            {
                get { return this.loveBook; }
                set { this.loveBook = value; }
            }
        }
    }
}

/*
--Test数据库，Person表以及存储过程创建脚本
 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Person](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[BirthDay] [datetime] NULL,
	[EMails] [nvarchar](max) NULL,
	[LoveDay] [nvarchar](50) NULL,
	[LoveColor] [nvarchar](50) NULL,
	[Money] [money] NULL,
	[LoveSong] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPerson]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[AddPerson]
	@Name NVARCHAR(50),
	@BirthDay DATETIME,
	@EMails NVARCHAR(MAX),
	@LoveDay NVARCHAR(50),
	@LoveColor NVARCHAR(50),
	@Money MONEY,
	@ID INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO Person (Name,BirthDay,EMails,LoveDay,LoveColor,Money) VALUES (@Name,@BirthDay,@EMails,@LoveDay,@LoveColor,@Money);
	SET @ID = SCOPE_IDENTITY();
END
' 
END

 */
